package cn.codergege.demo.main

import cn.codergege.demo.util.DbUtil
import groovy.sql.Sql

import java.sql.ResultSet
import java.text.SimpleDateFormat

/**
 * Created by codergege on 17-3-8.
 */
class DbQueryer {
    /*
    义乌小商品城
    义乌全部
    金华地区非义乌,
    省内/分地区包括金华
    省外(总数)
     */

    static main(args) {
        Sql sql = DbUtil.sql
        def sqlstr = "select count(*) from bm_qylx"
        /**
         * 一区: 33078223
         * 二区: 33078225
         * 三区: 33078240
         * 四区: 33078234
         * 五区: 33078238
         * 篁园市场: 33078210
         * 生产资料市场: 33078252
         */

        /**
         * 1. 地区
         * 1.1 义乌: 31245
         * 1.2 省内非义乌:
         */
        def total = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def total2016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def total2016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def dqyiwu = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and (b.sfzjhm like '330782%' or b.sfzjhm like '330725%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def dqyiwu2016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and (b.sfzjhm like '330782%' or b.sfzjhm like '330725%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def dqyiwu2016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and (b.sfzjhm like '330782%' or b.sfzjhm like '330725%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def dqsnfyiwu = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and b.sfzjhm like '33%'
and b.sfzjhm not like '330782%' and b.sfzjhm not like '330725%'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def dqsnfyiwu2016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and b.sfzjhm like '33%'
and b.sfzjhm not like '330782%' and b.sfzjhm not like '330725%'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def dqsnfyiwu2016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and b.sfzjhm like '33%'
and b.sfzjhm not like '330782%' and b.sfzjhm not like '330725%'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def dqsw = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and b.sfzjhm not like '33%'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
            def dqsw2016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and b.sfzjhm not like '33%'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
            def dqsw2016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.sfzjmc like '1'
and b.sfzjhm not like '33%'
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
//
        def csrq = """
select b.csrq from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.csrq is not null
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def csrq2016xz = """
select b.csrq from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.csrq is not null
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def csrq2016zx = """
select b.csrq from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.csrq is not null
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def whcd1 = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.whcd in (30, 20, 10, 8, 5, 6, 7)
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def whcd12016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.whcd in (30, 20, 10, 8, 5, 6, 7)
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def whcd12016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.whcd in (30, 20, 10, 8, 5, 6, 7)
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        // 大学以下
        def whcd2 = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.whcd in (90, 80, 70, 60, 50, 40, 1, 2, 3, 4)
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def whcd22016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.whcd in (90, 80, 70, 60, 50, 40, 1, 2, 3, 4)
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def whcd22016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.whcd in (90, 80, 70, 60, 50, 40, 1, 2, 3, 4)
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        // 男 = 1 女 = 2
        def gender1 = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.xb is not null and
(b.xb like '%1%' or b.xb like '%男%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def gender12016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.xb is not null and
(b.xb like '%1%' or b.xb like '%男%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def gender12016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.xb is not null and
(b.xb like '%1%' or b.xb like '%男%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def gender2 = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.xb is not null and
(b.xb like '%2%' or b.xb like '%女%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
"""
        def gender22016xz = """
select count(1) from hz_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.xb is not null and
(b.xb like '%2%' or b.xb like '%女%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and to_char(a.clrq, 'yyyy-MM-dd') >= '2016-01-01'
and to_char(a.clrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        def gender22016zx = """
select count(1) from bf_qyhznr a left outer join hz_qyfddbr b on a.nbxh = b.nbxh 
where 
b.xb is not null and
(b.xb like '%2%' or b.xb like '%女%')
and a.gxdw in (33078223, 33078225, 33078240, 33078234, 33078238, 33078210, 33078252)
and a.zt = 'X'
and to_char(a.bgzxrq, 'yyyy-MM-dd') >= '2016-01-01' 
and to_char(a.bgzxrq, 'yyyy-MM-dd') <= '2016-12-12'
"""
        println "total"
        queryMethod(sql, total)
        println '-' * 50
        println "地区"
        println '-' * 50
        println "义乌"
        queryMethod(sql, dqyiwu)
        println '-' * 50
        println "省内非义乌"
        queryMethod(sql, dqsnfyiwu)
        println '-' * 50
        println "省外"
        queryMethod(sql, dqsw)

        println ''
        println '-' * 50
        println "年龄"
        println '-' * 50
        // 小于　1980
        def nl1 = 0
        // [1980, 1990)
        def nl2 = 0
        // [1990, 2000)
        def nl3 = 0
        // [2000,)
        def nl4 = 0

        sql.eachRow csrq, {
            if(it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('1980-01-01')) nl1++
            if(it.csrq >= new SimpleDateFormat('yyyy-MM-dd').parse('1980-01-01')
                    && it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('1990-01-01'))
                nl2++
            if(it.csrq >= new SimpleDateFormat('yyyy-MM-dd').parse('1990-01-01')
                    && it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('2000-01-01'))
                nl3++
            if(it.csrq > new SimpleDateFormat('yyyy-MM-dd').parse('2000-01-01')) nl4++
        }
        println "小于 1980: $nl1"
        println "[1980-1990): $nl2"
        println "[1990-2000): $nl3"
        println "大于等于 2000: $nl4"
        println '-' * 50

        println ''
        println '-' * 50
        println "文化程度"
        println '-' * 50

        println "大学以上:"
        queryMethod sql, whcd1
        println "大学以下:"
        queryMethod sql, whcd2

        println ''
        println '-' * 50
        println "性别"
        println '-' * 50
        println '男:'
        queryMethod sql, gender1
        println '-' * 50
        println '女:'
        queryMethod sql, gender2

        println ''
        println '-' * 50
        println "**2016年新增**"
        println '-' * 50
        println "total"
        queryMethod(sql, total2016xz)

        println '-' * 50
        println "地区"
        println '-' * 50
        println "义乌"
        queryMethod(sql, dqyiwu2016xz)
        println '-' * 50
        println "省内非义乌"
        queryMethod(sql, dqsnfyiwu2016xz)
        println '-' * 50
        println "省外"
        queryMethod(sql, dqsw2016xz)

        println ''
        println '-' * 50
        println "年龄"
        println '-' * 50
        // 小于　1980
        def nl12016xz = 0
        // [1980, 1990)
        def nl22016xz = 0
        // [1990, 2000)
        def nl32016xz = 0
        // [2000,)
        def nl42016xz = 0

        sql.eachRow csrq2016xz, {
            if(it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('1980-01-01')) nl12016xz++
            if(it.csrq >= new SimpleDateFormat('yyyy-MM-dd').parse('1980-01-01')
                    && it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('1990-01-01'))
                nl22016xz++
            if(it.csrq >= new SimpleDateFormat('yyyy-MM-dd').parse('1990-01-01')
                    && it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('2000-01-01'))
                nl32016xz++
            if(it.csrq > new SimpleDateFormat('yyyy-MM-dd').parse('2000-01-01')) nl42016xz++
        }
        println "小于 1980: $nl12016xz"
        println "[1980-1990): $nl22016xz"
        println "[1990-2000): $nl32016xz"
        println "大于等于 2000: $nl42016xz"
        println '-' * 50

        println ''
        println '-' * 50
        println "文化程度"
        println '-' * 50

        println "大学以上:"
        queryMethod sql, whcd12016xz
        println "大学以下:"
        queryMethod sql, whcd22016xz

        println ''
        println '-' * 50
        println "性别"
        println '-' * 50
        println '男:'
        queryMethod sql, gender12016xz
        println '-' * 50
        println '女:'
        queryMethod sql, gender22016xz

        println ''
        println '-' * 50
        println "**2016年注销**"
        println '-' * 50
        println "total"
        queryMethod(sql, total2016zx)

            println '-' * 50
            println "地区"
            println '-' * 50
            println "义乌"
            queryMethod(sql, dqyiwu2016zx)
            println '-' * 50
            println "省内非义乌"
            queryMethod(sql, dqsnfyiwu2016zx)
            println '-' * 50
            println "省外"
            queryMethod(sql, dqsw2016zx)

        println ''
        println '-' * 50
        println "年龄"
        println '-' * 50
        // 小于　1980
        def nl12016zx = 0
        // [1980, 1990)
        def nl22016zx = 0
        // [1990, 2000)
        def nl32016zx = 0
        // [2000,)
        def nl42016zx = 0

        sql.eachRow csrq2016zx, {
            if(it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('1980-01-01')) nl12016zx++
            if(it.csrq >= new SimpleDateFormat('yyyy-MM-dd').parse('1980-01-01')
                    && it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('1990-01-01'))
                nl22016zx++
            if(it.csrq >= new SimpleDateFormat('yyyy-MM-dd').parse('1990-01-01')
                    && it.csrq < new SimpleDateFormat('yyyy-MM-dd').parse('2000-01-01'))
                nl32016zx++
            if(it.csrq > new SimpleDateFormat('yyyy-MM-dd').parse('2000-01-01')) nl42016zx++
        }
        println "小于 1980: $nl12016zx"
        println "[1980-1990): $nl22016zx"
        println "[1990-2000): $nl32016zx"
        println "大于等于 2000: $nl42016zx"
        println '-' * 50

        println ''
        println '-' * 50
        println "文化程度"
        println '-' * 50

        println "大学以上:"
        queryMethod sql, whcd12016zx
        println "大学以下:"
        queryMethod sql, whcd22016zx

        println ''
        println '-' * 50
        println "性别"
        println '-' * 50
        println '男:'
        queryMethod sql, gender12016zx
        println '-' * 50
        println '女:'
        queryMethod sql, gender22016zx

        sql.close()

    }

    static queryMethod(sql, querySql) {
        sql.query querySql, {ResultSet rs ->
            while (rs.next()) {
                println rs.getInt(1)
            }
        }
    }
}
